package fileToEXCEL;


import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @ClassName FileToEXCEL
 * @Description 导出EXCEL
 * @Author dzf
 * @Date 2022/6/10 23:14
 * @Version 1.0
 */

public class FileToEXCEL {

    private static String uploadDir = "C:\\Users\\dzf\\Downloads";

    public static void main(String[] args) {
        execute(new AnalogDataEXCEL().map());
    }

    public static void execute(Map ctx) {
        // TODO Auto-generated method stub
        String nameStr = "业绩查看明细";
        System.out.println("=========" + nameStr + "导出-run===========");
        //获取存放文件夹地址,存放在该项目doc文件夹下
        Date date = new Date();
        SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy");
        SimpleDateFormat formatter2 = new SimpleDateFormat("yyyyMM");
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String filePutPath = uploadDir + "/" + formatter1.format(date) + "/" + formatter2.format(date);
        System.out.println(nameStr + "导出--{}" + filePutPath);
        String filename = nameStr + "导出" + formatter.format(date);
        File file = makeExportExcel4HN(filePutPath, filename, ctx);
        if (null == file || !file.exists()) {
            System.out.println("文件创建失败--");
        }
        filename = file.getName();
        ctx.put("download_filePath", formatter1.format(date) + "/" + formatter2.format(date) + "/" + filename);
        ctx.put("download_fileName", nameStr + formatter.format(date) + ".xlsx");
        System.out.println("-生成Excel文件成功！");
    }

    /**
     *
     * @Description 文件下载内容处理
     * @author dzf
     * @date 2022/6/16 17:22
     * @params [outPutPath 文件输出路径, filename 输出文件名, ctx 前端传值]
     * @return java.io.File
     * @Version1.0
     **/
    public static File makeExportExcel4HN(String outPutPath, String filename, Map ctx) {
        File excelFile = null;
        SXSSFWorkbook workbook = null;
        OutputStream out = null;
        try {
            excelFile = new File(outPutPath + File.separator + filename + ".xlsx");
            File parent = excelFile.getParentFile();
            if (parent != null && !parent.exists()) {
                if (!parent.mkdirs()) {
                    return excelFile;
                }
            }
            if (excelFile.exists()) {
                // 文件已经存在
                return excelFile;
            }
            if (!excelFile.createNewFile()) {
                return excelFile;
            }

            out = new FileOutputStream(excelFile);
            // 声明一个工作簿
            workbook = new SXSSFWorkbook(500);// 每次缓存500条到内存，其余写到磁盘
            Map<String, Object> map = getTableHead(ctx);//表头的值
            
            //区域
            String[] headers1 = new String[]{"区域", "存款余额", "存款日均", "贷款余额", "贷款日均", "低成本存款余额", "低成本存款日均",
                    "非房非持牌NBFI贷款余额", "非房非持牌NBFI贷款日均"};
            String[] fields1 = new String[]{"SUM_NAME", "N1", "N2", "N3", "N4", "N5", "N6", "N7", "N8"};
            String[] nameNO1 = new String[]{};//需要合并列的id
            String str1 = "区域";
            //分行
            String[] headers2 = new String[]{"区域", "分行", "存款余额", "存款日均", "贷款余额", "贷款日均", "低成本存款余额", "低成本存款日均",
                    "非房非持牌NBFI贷款余额", "非房非持牌NBFI贷款日均"};
            String[] fields2 = new String[]{"UP_ORG_NAME", "SUM_NAME", "N1", "N2", "N3", "N4", "N5", "N6", "N7", "N8"};
            String[] nameNO2 = new String[]{"UP_ORG_NO"};//需要合并列的id
            String str2 = "分行";

            //团队
            String[] headers3 = new String[]{"区域", "分行", "部门", "存款余额", "存款日均", "贷款余额", "贷款日均", "低成本存款余额", "低成本存款日均",
                    "非房非持牌NBFI贷款余额", "非房非持牌NBFI贷款日均"};
            String[] fields3 = new String[]{"ORG_NAME", "UP_ORG_NAME", "SUM_NAME",  "N1", "N2", "N3", "N4", "N5", "N6", "N7", "N8"};
            String[] nameNO3 = new String[]{"ORG_ID", "UP_ORG_NO"};
            String str3 = "团队";

            String HRCY = (String) ctx.get("HRCY");
            if ("1".equals(HRCY) || "2".equals(HRCY)) {
                //区域
                ctx.put("HRCY", "2");
                createSheet(str1, headers1, fields1, nameNO1, workbook, queryListMap(ctx), map, 0);
                //分行
                ctx.put("HRCY", "3");
                createSheet(str2, headers2, fields2, nameNO2, workbook, queryListMap(ctx), map, 1);
                //团队
                ctx.put("HRCY", "4");
                createSheet(str3, headers3, fields3, nameNO3, workbook, queryListMap(ctx), map, 3);
            } else if ("3".equals(HRCY)) {
                //分行
                ctx.put("HRCY", "3");
                createSheet(str2, headers2, fields2, nameNO2, workbook, queryListMap(ctx), map, 1);
                //团队
                ctx.put("HRCY", "4");
                createSheet(str3, headers3, fields3, nameNO3, workbook, queryListMap(ctx), map, 3);
            } else if ("4".equals(HRCY)) {
                //团队
                ctx.put("HRCY", "4");
                createSheet(str3, headers3, fields3, nameNO3, workbook, queryListMap(ctx), map, 3);
            }
            workbook.write(out);
            out.flush();
            workbook.close();
            if (!excelFile.setWritable(true, false)) {// 关闭写权限
                throw new Exception("文件权限修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (null != out) {
                    out.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return excelFile;
    }

    /**
     *
     * @Description 输出文档
     * @author dzf
     * @date 2022/6/16 17:04
     * @params [sheetStr sheet页名, headers 表格头行内容, fields 表格头行取值字段名,
     *          nameNO 需要合并列 列名的id, workbook, listData 表格内容, map 表头内容, num 第几列开始换表格样式]
     * @return void
     * @Version1.0
     **/
    private static void createSheet(String sheetStr, String[] headers, String[] fields, String[] nameNO, SXSSFWorkbook workbook,
                             List<Map> listData, Map map, int num) throws Exception {
        // 生成一个表格
        Sheet sheet = workbook.createSheet(sheetStr);
        // 设置表格默认宽度为30字节
        sheet.setDefaultColumnWidth((int) 15);
        // 生成样式
        CellStyle style = workbook.createCellStyle();
        CellStyle headerStyle = createHeaderStyle(workbook);
        // 生成样式2
//		CellStyle style2 = workbook.createCellStyle();
        CellStyle style2 = createData1CellStyle(workbook);
        // 生成样式3
        CellStyle style3 = createData2CellStyle(workbook);

        //TODO 表头内容初始化
        String[] str = new String[]{"A.选项：", "时间维度：", "数据时间：", "机构/团队：", "数据口径：", "双计口径：", "B.币种单位：", "C.报表维度："};
        String[] str1 = new String[]{"", "TIME_DIMENSION", "MIS_DATA_DT", "ORG_NAME", "RCV_NAME", "IND_TYPE", "BIZHONG", "TABLE_NAME"};
        for (int i = 0; i < str.length; i++) {
            Row row = sheet.createRow(i);
            Cell cell = null;
            RichTextString richString = null;
            cell = row.createCell(0);
            cell.setCellStyle(style2);
            richString = new HSSFRichTextString(str[i]);
            cell.setCellValue(richString);
            //内容
            cell = row.createCell(1);
            cell.setCellStyle(style2);
            richString = new HSSFRichTextString((String) map.get(str1[i]));
            cell.setCellValue(richString);
        }

        //TODO 合并行
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,1));
        sheet.addMergedRegion(new CellRangeAddress(0,7,7,8));

        //TODO 产生表格标题 行
        Row row = sheet.createRow(8);
        Cell cell = null;
        RichTextString richString = null;
        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            richString = new HSSFRichTextString(headers[i]);
            cell.setCellValue(richString);
        }

        // 遍历集合数据，产生数据行
        Iterator<Map> it = listData.iterator();
        //TODO 表内容开始行数
        int index = 9;
        int m = 0;
        int[] start = new int[nameNO.length];
        int[] end = new int[nameNO.length];
        for (int i = 0; i < start.length; i++) {
            //TODO 合并列初始化 表内容开始行数
            start[i] = end[i] = 9;
        }
        while (it.hasNext()) {
            row = sheet.createRow(index);
            Map t = (Map) it.next();

            for (int i = 0; i < fields.length; i++) {
                cell = row.createCell(i);
                //TODO 不同样式列
                if (i > num) {
                    cell.setCellStyle(style3);
                } else {
                    cell.setCellStyle(style2);
                }
                String fieldName = fields[i];

                if (t.get(fieldName) != null) {
                    try {
                        //TODO 不同样式列输出
                        if (i > num) {
                            cell.setCellValue(Double.valueOf(t.get(fieldName).toString()));
                        } else {
                            richString = new HSSFRichTextString(String.valueOf(t.get(fieldName)));
                            cell.setCellValue(richString);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw new Exception("Excel文件创建失败!");
                    }
                }
            }

            //TODO 第二列向上比较相同合并
            if (m >= 1) {
                //向上比较相同合并
                for (int i = 0; i < nameNO.length; i++) {
                    if (t.get(nameNO[i]) != null && t.get(nameNO[i]).equals(listData.get(m - 1).get(nameNO[i]))) {
                        end[i] = index;
                    } else if (start[i] == end[i]) {
                        start[i] = index;
                        end[i] = index;
                    } else {
                        sheet.addMergedRegion(new CellRangeAddress(start[i], end[i], i, i));//(起始行，结束行，起始列，结束列)
                        start[i] = index;
                        end[i] = index;
                    }
                }
            }

            //TODO 最后一行合并
            if (m == listData.size() - 1) {
                for (int i = 0; i < nameNO.length; i++) {
                    if (start[i] != end[i]) {
                        sheet.addMergedRegion(new CellRangeAddress(start[i], end[i], i, i));//(起始行，结束行，起始列，结束列)
                    }
                }
            }
            m++;
            index++;
        }
    }

    /**
     *
     * @Description 获取表内容
     * @author dzf
     * @date 2022/6/16 17:23
     * @params [ctx]
     * @return java.util.List<java.util.Map>
     * @Version1.0
     **/
    private static List<Map> queryListMap(Map ctx) {
        List<Map> listData = new ArrayList<>();
        if ("2".equals(ctx.get("HRCY"))) {
            listData = new AnalogDataEXCEL().QYList();
        } else if ("3".equals(ctx.get("HRCY"))) {
            listData = new AnalogDataEXCEL().FHList();
        } else if ("4".equals(ctx.get("HRCY"))) {
            listData = new AnalogDataEXCEL().TDList();
        }
        return listData;
    }

    /**
     *
     * @Description 获取表头内容
     * @author dzf
     * @date 2022/6/16 17:24
     * @params [ctx]
     * @return java.util.Map<java.lang.String,java.lang.Object>
     * @Version1.0
     **/
    private static Map<String, Object> getTableHead(Map ctx) {
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("BIZHONG", "百万元人民币");//币种单位
        map.put("TABLE_NAME", "业绩查看明细表");//币种单位
        String DATA_DT = (String) ctx.get("MIS_DATA_DT");
        map.put("MIS_DATA_DT", DATA_DT);//数据时间
//		map.put("ORG_NAME", ctx.get("ORG_NAME"));//机构/人员
        String ORG_NAME = (String) ctx.get("HRCY");
        if ("1".equals(ORG_NAME)) {
            map.put("ORG_NAME", "总行");
        } else if ("2".equals(ORG_NAME)) {
            map.put("ORG_NAME", "区域");
        } else if ("3".equals(ORG_NAME)) {
            map.put("ORG_NAME", "分行");
        } else if ("4".equals(ORG_NAME)) {
            map.put("ORG_NAME", "团队");
        } else if ("5".equals(ORG_NAME)) {
            map.put("ORG_NAME", "客户经理");
        }

        List<Map> typeList = (List<Map>) ctx.get("typeList");
        String IND_TYPE = "";
        for (Map map2 : typeList) {
            String str = (String) map2.get("IND_TYPE");
            if ("H".equals(str)) {
                str = "Hard";
            } else if ("SN".equals(str)) {
                str = "Soft-onshore";
            } else if ("SF".equals(str)) {
                str = "Soft-offshore";
            }
            IND_TYPE = IND_TYPE + str + ",";
        }
        IND_TYPE = IND_TYPE.substring(0, IND_TYPE.length() - 1);
        map.put("IND_TYPE", IND_TYPE);//双计口径

        String RCV_NAME = (String) ctx.get("RCV_NAME");
        if ("1".equals(RCV_NAME)) {
            RCV_NAME = "MTD";
        } else {
            RCV_NAME = "YTD";
        }
        map.put("RCV_NAME", RCV_NAME);//数据口径

        String TIME_DIMENSION = (String) ctx.get("TIME_DIMENSION");
        if ("1".equals(TIME_DIMENSION)) {
            TIME_DIMENSION = "日";
        } else {
            TIME_DIMENSION = "月";
        }
        map.put("TIME_DIMENSION", TIME_DIMENSION);//时间维度
        return map;
    }

    /**
     *
     * @Description 表头样式
     * @author dzf
     * @date 2022/6/16 17:24
     * @params [workbook]
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @Version1.0
     **/
    private static CellStyle createHeaderStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true); // 自动换行
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//		HSSFDataFormat format = wb.createDataFormat();
//		cellStyle.setDataFormat(format.getFormat("￥#,##0"));
//		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        return cellStyle;
    }

    /**
     *
     * @Description 字符串样式
     * @author dzf
     * @date 2022/6/16 17:25
     * @params [workbook]
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @Version1.0
     **/
    private static CellStyle createData1CellStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true); // 自动换行
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
//		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//		HSSFDataFormat format = wb.createDataFormat();
//		cellStyle.setDataFormat(format.getFormat("￥#,##0"));
//		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        return cellStyle;
    }

    /**
     *
     * @Description 数值样式（带千分位）
     * @author dzf
     * @date 2022/6/16 17:25
     * @params [wb]
     * @return org.apache.poi.ss.usermodel.CellStyle
     * @Version1.0
     **/
    private static CellStyle createData2CellStyle(SXSSFWorkbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setWrapText(true); // 自动换行
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
//		cellStyle.setFillForegroundColor((short) 13);
//		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        DataFormat format = wb.createDataFormat();
        cellStyle.setDataFormat(format.getFormat("#,##0.0;-#,##0.0"));
//		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        return cellStyle;
    }
}

